<?php 
/**
 * alltosun.com 导出Excel
 * ============================================================================
 * 版权所有 (C) 2009-2011 北京互动阳光科技有限公司，并保留所有权利。
 * 网站地址: http://www.alltosun.com
 * ----------------------------------------------------------------------------
 * 许可声明：这是一个开源程序，未经许可不得将本软件的整体或任何部分用于商业用途及再发布。
 * ============================================================================
 * $Author: 高昂 (gaoa@alltosun.com) $
 * $Date: 2016-3-16 上午10:54:56 $
 * $Id: $
 */


class Action
{
    
    private $col_arr = array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z');
    
    /**
     * worksheet(工作表)和title_list(标题)和data_list(数据)的个数一一对应
     */
    public function export_list()
    {
        $file_name = Request::getParam('file_name', '统计数据');
        $worksheet_list = array(
            '合伙人&掌柜版订单统计',
            '合伙人订单明细',
            '掌柜订单明细'
        );
        $title_list = array(
            array(
                // 属性
                'property' => array(
                    'text_align'     => 'center',
                    'font_size'      => '20', // 字体大小
                    'font_weight'    => 'bold', // 字体粗细
                    // 'font_family' => '微软雅黑', // 字体
                    'color'          => 'black', // 字体颜色
                    // 'width' => '', // 单元格宽度
                    // 'height' => '', // 单元格高度
                    // 'colspan'        => 1, // 合并几列
                    // 'rowspan'        => 1, // 合并几行
                ),
                'dealer' => array(
                    'title' => '合伙人APP安装以及订单统计',
                    'content' => array(
                        'property'                   => array(
                            'font_size'   => 11,
                            'font_weight' => 'bold',
                            'colspan'     => 9,
                            'rowspan'     => 1
                        ),
                        'add_time'                   => array('title' => '日期'),
                        'today_add_dealer_num'       => array('title' => '合伙人今日新增APP'),
                        'today_dealer_new_order_num' => array('title' => '新增订单'),
                        'dealer_order_num'           => array('title' => '订单总数'),
                        'delivery_order_num'         => array('title' => '已出货'),
                        'prepare_delivery_order_num' => array('title' => '待发货'),
                        'all_dealer_stock'           => array('title' => '合伙人总库存'),
                        'marketing_num'              => array('title' => '总员工数'),
                        'dealer_num'                 => array('title' => '合伙人APP总数'),
                    ),
                ),
                'store'  => array(
                    'title' => '掌柜版APP安装以及订单统计',
                    'content' => array(
                        'property' => array(
                            'background_color' => 'FF92D050', // 单元格的背景色(设置母标题背景色)
                            'font_weight'      => 'bold',
                            'font_size'        => 11,
                            'colspan'          => 11,
                            'rowspan'          => 1,
                        ),
                        'today_add_store_num'       => array('title' => '今日新增掌柜版APP'),
                        'dealer_sign_store_num'     => array('title' => '合伙人签约'),
                        'marketing_sign_store_num'  => array('title' => '员工签约'),
                        'trial_store_num'           => array('title' => '未审核'),
                        'normal_store_num'          => array('title' => '正常营业'),
                        'stop_store_num'            => array('title' => '撤店'),
                        'is_stock_store_num'        => array('title' => '已进货门店'),
                        'not_stock_store_num'       => array('title' => '未进货门店'),
                        'store_new_order_num'       => array('title' => '新增订单'),
                        'store_order_num'           => array('title' => '订单总数'),
                        'store_num'                 => array('title' => '掌柜版APP总数'),
                    ),
                ),
            ),
            array(
                array(
                    'property' => array(
                        'font_size' => 11,
                        'font_weight' => 'bold',
                    ),
                    'add_time'    => array('title' => '日期'),
                    'order_id'    => array('title' => '订单编号'),
                    'progress1'   => array('title' => '待收款'),
                    'progress3'   => array('title' => '待配送'),
                    'progress5'   => array('title' => '配送中'),
                    'progress10'  => array('title' => '配送完成'),
                    'progress15'  => array('title' => '撤销订单'),
                    'reason' => array('title' => '撤销原因'),
                )
            ),
            array(
                array(
                    'property' => array(
                        'font_size' => 11,
                        'font_weight' => 'bold',
                    ),
                    'id' => array('title' => '店铺id'),
                    'store_title' => array('title' => '店铺名称'),
                    'store_type' => array('title' => '门店类型'),
                    'add_time' => array('title' => '添加时间'),
                    'store_status' => array('title' => '门店状态'),
                    'sign_user' => array('title' => '签约人(角色)'),
                    'is_ever_stock' => array('title' => '是否进过货'),
                    'stock_num' => array('title' => '进货数量'),
                )
                
                    
            )
        );
        
        // 数据列表
        $data_list = array(
            array(
                'data' => $this->get_dealer_store_list(),
            ),
            array(
                'data' => $this->get_dealer_order_list(),
            ),
            array(
                'data' => $this->get_store_data_list(),
            ),
        );
        
//         echo '<pre>';
//         print_r($worksheet_list);
//         print_r($title_list);
//         print_r($data_list);
        
//         exit;
        // 执行导出execl
        $this->exec_export($file_name, $worksheet_list, $title_list, $data_list);
        
    }
    
    
    /**
     * 获取指定一天的统计数据
     * @param unknown $time 时间戳
     * @return number
     */
    public function get_admin_data($time = '')
    {
        if (!$time)  $time = time();
        
        $admin_data_list = array();
        
        // 经销商相关数据
        // 经销商数量
        $admin_data_list['dealer_num'] = user_helper::get_user_num_by_role_id(user_config::AGENCY, array('`add_time` <= '=>date('Y-m-d', $time).' 23:59:59'));
        
        // 今日新增：多少人增长率多少
        $today_add_dealer_num = user_helper::get_user_num_by_role_id(user_config::AGENCY, array('`add_time` LIKE '=>date('Y-m-d', $time).'%'));
        $before_dealer_num    = user_helper::get_user_num_by_role_id(user_config::AGENCY, array('`add_time` < '=>date('Y-m-d', $time).' 00:00:00'));
        
        $admin_data_list['today_add_dealer_num'] = $today_add_dealer_num;
        $admin_data_list['dealer_growth_rate']   = round(($today_add_dealer_num / $before_dealer_num) * 100, 2);
        
        // new 今日新增加的订单
        $admin_data_list['today_dealer_new_order_num'] = order_helper::get_order_num_by_progress(array(3, 5, 10), 1, array('is_enable'=>1, 'status'=>1, '`add_time` LIKE '=>date('Y-m-d', $time).'%'));
        
        // 成交订单数/已收到款项
        $admin_data_list['dealer_order_num']   = order_helper::get_order_num_by_progress(array(3,5, 10), 1, array('is_enable'=>1, 'status'=>1, '`add_time` <= '=>date('Y-m-d', $time).' 23:59:59'));
        $admin_data_list['dealer_order_money'] = order_helper::get_order_money_by_progress(array(3,5, 10), 1,  array('is_enable'=>1, 'status'=>1, '`add_time` <= '=>date('Y-m-d', $time).' 23:59:59'));
        
        // new 已成功出货的订单数
        $admin_data_list['delivery_order_num'] = order_helper::get_order_num_by_progress(array(5, 10), 1, array('is_enable' => 1, 'status' => 1, '`add_time` <= ' => date('Y-m-d', $time).' 23:59:59'));
        // 已成功出货：多少箱 (总部向合伙人发货)
        $admin_data_list['delivery_goods_num'] = order_helper::get_order_goods_num(array(5, 10), 1, array('is_enable'=>1, 'status'=>1, '`add_time` <= '=>date('Y-m-d', $time).' 23:59:59'));
        
        // 待收款订单/等待收款项：订单数量/（未成交金额）多少元  @FIXME
        $admin_data_list['prepare_dealer_order_num']   = order_helper::get_order_num_by_progress(1, 1, array('is_enable'=>1, 'status'=>1, '`add_time` <= '=>date('Y-m-d', $time).' 23:59:59'));
        $admin_data_list['prepare_dealer_order_money'] = order_helper::get_order_money_by_progress(1, 1, array('is_enable'=>1, 'status'=>1, '`add_time` <= '=>date('Y-m-d', $time).' 23:59:59'));
        
        // 等待发货：多少箱 (只要提交订单就算是) @FIXME(名字不对 原来是prepare_delivery_order_num)
        $admin_data_list['prepare_delivery_goods_num'] = order_helper::get_order_goods_num(array(1, 3), 1, array('is_enable'=>1, 'status'=>1, '`add_time` <= '=>date('Y-m-d', $time).' 23:59:59'));
        // new 等待发货的订单数
        $admin_data_list['prepare_delivery_order_num'] = order_helper::get_order_num_by_progress(array(1, 3), 1, array('is_enable'=>1, 'status'=>1, '`add_time` <= ' => date('Y-m-d', $time).' 23:59:59'));
        
        // 合伙人总库存：多少箱
        $dealer_ids = user_helper::get_role_user_ids(user_config::AGENCY, '' , array('is_enable'=>1, '`add_time` <= '=>date('Y-m-d', $time).' 23:59:59'));
        $admin_data_list['all_dealer_stock'] = dealers_helper::get_dealer_stock($dealer_ids);
        
        // 合伙人总员工：多少人 (市场人员的总数量)
        $store_list = store_helper::get_store_list_by_dealer_id($dealer_ids, array('is_enable'=>1, 'is_delete'=>1, '`add_time` <= '=>date('Y-m-d', $time).' 23:59:59' ));
        $marketing_ids = array();
        if ($store_list) {
            foreach ($store_list as $k=>$v) {
                if (!in_array($v['marketing_id'], $marketing_ids) && $v['marketing_id'] != $v['agency_id'] && $v['marketing_id'] != 0) {
                    array_push($marketing_ids, $v['marketing_id']);
                }
            }
        }
        $admin_data_list['marketing_num'] = count(array_unique($marketing_ids));
        
        // 终端店铺相关数据(分店)
        $admin_data_list['store_num'] = store_helper::get_all_store_num(array('`add_time` <= '=>date('Y-m-d', $time).' 23:59:59'));
        // 今日新增：多少门店增长率多少
        $today_add_store_num = user_helper::get_user_num_by_role_id(user_config::STORE_MANAGER, array('`add_time` LIKE '=>date('Y-m-d', $time).'%'));
        $admin_data_list['today_add_store_num'] = $today_add_store_num;
        $before_store_num = user_helper::get_user_num_by_role_id(user_config::STORE_MANAGER, array('`add_time` < '=>date('Y-m-d', $time).' 00:00:00'));
        $admin_data_list['store_growth_rate'] = round($today_add_store_num / $before_store_num * 100, 2);
        // 已进货门店数量：(必须是确认收货的门店)
        $admin_data_list['is_stock_store_num'] = user_helper::get_user_num_by_role_id(user_config::STORE_MANAGER, array('`score` > '=>0, '`add_time` <= '=>date('Y-m-d', $time).' 23:59:59'));
        // 未进货门店数量：(其他)
        $admin_data_list['not_stock_store_num'] = $admin_data_list['store_num'] - $admin_data_list['is_stock_store_num'];
        // 门店订单总量：(所有订单)
        $admin_data_list['store_order_num'] = order_helper::get_order_num_by_progress(array(1, 3, 5, 10, 15), 5, array('status'=>1, 'is_enable'=>1, '`add_time` <= '=>date('Y-m-d', $time).' 23:59:59'));
        // 今日新增订单量：(统计处在配送中的订单,不会撤销的订单)
        $admin_data_list['store_new_order_num'] = order_helper::get_order_num_by_progress(array(5, 10), 5, array('status'=>1, 'is_enable'=>1, '`add_time` LIKE '=>date('Y-m-d', $time).'%'));
        $admin_data_list['add_time'] = date('Y-m-d', $time);
        
        /**
         * 'dealer_sign_store_num'     => array('title' => '合伙人签约'),
                        'marketing_sign_store_num'  => array('title' => '员工签约'),
                        'trial_store_num'           => array('title' => '未审核'),
                        'normal_store_num'          => array('title' => '正常营业'),
                        'stop_store_num'            => array('title' => '撤店'),
         */
        
        // new 合伙人签约的门店数量
        // 获取所有有效的经销商id
        $dealer_ids = user_helper::get_role_user_ids(user_config::AGENCY, '', array('is_enable'=>1, 'status'=>1, '`add_time` <= '=>date('Y-m-d', $time).' 23:59:59'));
        $admin_data_list['dealer_sign_store_num'] = store_helper::get_all_store_num(array('`add_time` <= '=>date('Y-m-d', $time).' 23:59:59', 'marketing_id'=>$dealer_ids));
        // new 市场人员签约的门店数量
        $marketing_ids = user_helper::get_role_user_ids(user_config::MARKETING, '', array('is_enable' => 1, 'status' => 1, '`add_time` <= ' => date('Y-m-d', $time).' 23:59:59'));
        $admin_data_list['marketing_sign_store_num'] = store_helper::get_all_store_num(array('`add_time` <= '=>date('Y-m-d', $time).' 23:59:59', '`marketing_id` != '=>$dealer_ids, 'marketing_id'=>$marketing_ids));
        // new 待审核门店数
        $admin_data_list['trial_store_num'] = store_helper::get_all_store_num(array('`add_time` <= '=>date('Y-m-d', $time).' 23:59:59', 'status'=>1));
        // new 正常营业的门店数
        $admin_data_list['normal_store_num'] = store_helper::get_all_store_num(array('`add_time` <= '=>date('Y-m-d', $time).' 23:59:59', 'status'=>2));
        // new 已撤店门店数
        $admin_data_list['stop_store_num'] = store_helper::get_all_store_num(array('`add_time` <= '=>date('Y-m-d', $time).' 23:59:59', 'status'=>3));
        
        
        return $admin_data_list;
    }
    
    /**
     * 获取合伙人订单明细
     * @param unkno
     * wn $export_list
     */
    public function get_dealer_order_list()
    {
        // 经销商向总部进货订单列表
        $order_list = array();
        $order_filter['type']      = 1;
        $order_filter['status'] = 1;
        $order_filter['is_enable'] = 1;
        
        $order_list = _model('order')->getList($order_filter);
        if ($order_list) {
            foreach ($order_list as $k => $v) {
                $order_list[$k]['progress1']  = '';
                $order_list[$k]['progress3']  = '';
                $order_list[$k]['progress5']  = '';
                $order_list[$k]['progress10'] = '';
                $order_list[$k]['progress15'] = '';
                $order_list[$k]['progress'.$v['progress']] = '√';
                $order_list[$k]['order_id'] = ' '.order_helper::order_id_encode($v['id']);
            }
        }
        
        
        return $order_list;
    }
    
    /**
     * 获取商家明细
     * @param u
     * nknown $export_list
     */
    public function get_store_data_list()
    {
        $store_list = array();
        
        $filter['is_enable'] = 1;
        $filter['is_delete'] = 1;
        $filter['`agency_id` != '] = 0;
        
        $store_list = _model('store')->getList($filter, ' ORDER BY `id` DESC');
        if ($store_list) {
            $type_arr = store_config::$type_arr;
            foreach ($store_list as $k => $v) {
                $user_info = _uri('user', @$v['owner_id']);
                if ($user_info) {
                    $store_list[$k]['user_name'] = $user_info['user_name'];
                    $store_list[$k]['mobile']    = $user_info['mobile'];
                }
                if (isset($type_arr[$v['type']])) {
                    $store_list[$k]['type_name']  = $type_arr[$v['type']];
                }
                if ($v['agency_id']) {
                    $dealer_info = _uri('user', $v['agency_id']);
                    if ($dealer_info) {
                        $store_list[$k]['dealer_name'] = $dealer_info['realname'];
                        $store_list[$k]['dealer_mobile'] = $dealer_info['mobile'];
                    }
                }
                if (!$v['sign_user_id']) {
                    continue;
                }
                $sign_user_role_id = user_helper::get_user_role_id($v['sign_user_id']);
                $sign_user_info = user_helper::get_user_info($v['sign_user_id']);
                $store_list[$k]['sign_user_type'] = user_config::$role_arr[$sign_user_role_id];
                $store_list[$k]['sign_user_role_id'] = user_config::$role_arr[$sign_user_role_id];
                $store_list[$k]['sign_user_info'] = $sign_user_info;
                $store_list[$k]['sign_user'] = $sign_user_info['realname'].'（'.$store_list[$k]['sign_user_type'].'）';
                // 是否进货及进货额度
                $store_list[$k]['is_ever_stock'] = store_helper::get_store_stock_money($v['id'], 10);
                $store_list[$k]['stock_num'] = 0;
                if ($store_list[$k]['is_ever_stock']) {
                    $store_list[$k]['stock_num'] = $store_list[$k]['is_ever_stock'].'箱';
                    $store_list[$k]['is_ever_stock'] = '是';
                } else {
                    $store_list[$k]['is_ever_stock'] = '否';
                    $store_list[$k]['stock_num'] = '0箱';
                }
                $store_list[$k]['store_title'] = $store_list[$k]['title'].$store_list[$k]['title_branch'];
                $store_list[$k]['store_stock_money'] = store_helper::get_store_stock_money($v['id']);
                $store_list[$k]['store_type'] = store_config::$type_arr[$v['type']];
                if ($v['status'] == 1) {
                    $store_list[$k]['store_status'] = '未审核';
                } elseif ($v['status'] == 2) {
                    $store_list[$k]['store_status'] = '正常营业';
                } else {
                    $store_list[$k]['store_status'] = '已撤店';
                }
                
            }
        }
        
        return $store_list;
        
        
    }
    
    
    private function get_dealer_store_list()
    {
        // $set_up_time = '2016-02-18 15:55:00';
        $set_up_time = '2016-02-18 23:59:59';
        $data_list = array();
        $time = strtotime($set_up_time);
        for ($i=0, $cur_time = $time; date('Ymd', $cur_time) < date('Ymd'); $i++) {
            $cur_time = strtotime('+'.$i.' day', $time);
            array_push($data_list, $this->get_admin_data($cur_time));
        }
        return $data_list;
    }
    
    /**
     * 获取经销商订单信息
     */
    
    
    /**
     * 执行导出execl
     * @param $file_name 文件名
     * @param $export_list 数据列表
     */
    private function exec_export($file_name, $worksheet_list, $title_list, $data_list)
    {
        if (!$file_name || !$worksheet_list || !$title_list || !$data_list) {
            return false;
        }
        
        //初始化excel对象
        $objPHPExcel = new PHPExcel();
        $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
        
        $objPHPExcel->getDefaultStyle()->getFont()->setName('宋体');
        // 建立循环索引
        $k = 0;
        foreach ($worksheet_list as $worksheet) {
            
            if ($k) {
                //添加一个新的worksheet
                $objPHPExcel->createSheet();
            }
            // 设置工作表名称
            $objPHPExcel->getSheet($k)->setTitle($worksheet);
            $start_row = 1;
            // 统计需要多少列
            $col_num = 0;
            $col_title_alias_arr = array();
            $col_title_arr = array();
            if (count($title_list[$k]) > 1) {
                foreach ($title_list[$k] as $kk => $v) {
                    if ($kk == 'property') {
                        continue ;
                    }
                    
                    $col_num += count($title_list[$k][$kk]['content']) - 1;
                    
                    array_push($col_title_alias_arr, array_slice(array_keys($v['content']), 1));
                    foreach ($v['content'] as $kkk => $vv) {
                        if ($kkk == 'property') {
                            continue;
                        }
                        array_push($col_title_arr, $vv['title']);
                    }
                    
                }
                $start_row = 2;
                
            } else {
                $col_num = count($title_list[$k][0]) - 1;
                $col_title_alias_arr = array_slice(array_keys($title_list[$k][0]), 1);
                foreach ($title_list[$k][0] as $kkk => $vv) {
                    if ($kkk == 'property') {
                        continue;
                    }
                    array_push($col_title_arr, $vv['title']);
                }
                $start_row = 1;
            }
            $col_arr = array_slice($this->col_arr, 0, $col_num);
            
            // 设置单元格属性
            if ($start_row == 2) {
                // 合并单元格
                $objPHPExcel->getSheet($k)->mergeCells("A1:I1");
                $objPHPExcel->getSheet($k)->mergeCells("J1:T1");
                
                // 设置单元格的高度
                $objPHPExcel->getSheet($k)->getRowDimension(1)->setRowHeight(35);                
                //设置单位格内容
                $objPHPExcel->getSheet($k)->setCellValue('A1', $title_list[0]['dealer']['title']);
                $objPHPExcel->getSheet($k)->setCellValue('J1', $title_list[0]['store']['title']);
                //格式化数字为字符串，避免导致数字默认右对齐单元格
                // $objPHPExcel->getSheet($k)->getStyle('A1')->getNumberFormat()->setFormatCode("@");
                // $objPHPExcel->getSheet($k)->getStyle('J1')->getNumberFormat()->setFormatCode("@");
                //设置自动换行
                $objPHPExcel->getSheet($k)->getStyle('A1')->getAlignment()->setWrapText(true);
                $objPHPExcel->getSheet($k)->getStyle('J1')->getAlignment()->setWrapText(true);
                // 标题字体加粗
                $objPHPExcel->getSheet($k)->getStyle('A1')->getFont()->setBold(true);
                $objPHPExcel->getSheet($k)->getStyle('J1')->getFont()->setBold(true);
                // 设置字体大小
                $objPHPExcel->getSheet($k)->getStyle('A1')->getFont()->setSize(20);
                $objPHPExcel->getSheet($k)->getStyle('J1')->getFont()->setSize(20);
                // 设置字体居中
                //设置对齐方式
                $objPHPExcel->getSheet($k)->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                $objPHPExcel->getSheet($k)->getStyle('J1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                $objPHPExcel->getSheet($k)->getStyle('A1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
                $objPHPExcel->getSheet($k)->getStyle('J1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
                
                // 设置填充颜色
                //设置填充颜色
                $objPHPExcel->getSheet($k)->getStyle('J1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
                $objPHPExcel->getSheet($k)->getStyle('J1')->getFill()->getStartColor()->setARGB('FF92D050');
                
                // $objPHPExcel->getSheet($k)->getColumnDimension('A')->setAutoSize(true);
                // $objPHPExcel->getSheet($k)->getColumnDimension('J')->setAutoSize(true);
                // 设置边框
                //设置边框
//                 $objPHPExcel->getSheet($k)->getStyle('J1')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
//                 // $objPHPExcel->getSheet($k)->getStyle('J1')->getBorders()->getTop()->getColor()->setARGB('FFFF0000');// color
//                 $objPHPExcel->getSheet($k)->getStyle('J1')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
//                 $objPHPExcel->getSheet($k)->getStyle('J1')->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
//                 $objPHPExcel->getSheet($k)->getStyle('J1')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
                
//                 // $objPHPExcel->getSheet($k)->getStyle('J1')->getBorders()->getTop()->getColor()->setARGB('FFFF0000');// color
//                 $objPHPExcel->getSheet($k)->getStyle('A1')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
//                 $objPHPExcel->getSheet($k)->getStyle('A1')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
//                 $objPHPExcel->getSheet($k)->getStyle('A1')->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
//                 $objPHPExcel->getSheet($k)->getStyle('A1')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
                
            }
            
            $i = 0;
            // 设置文档属性
            foreach ($col_arr as $key => $val) {
                //设置列的宽度
                if ($k == 0) {
                    if (in_array($val, array('I', 'T'))) {
                        $objPHPExcel->getSheet($k)->getColumnDimension($val)->setWidth(15);
                    } elseif (in_array($val, array('B', 'J'))) {
                        $objPHPExcel->getSheet($k)->getColumnDimension($val)->setWidth(20);
                    } elseif (in_array($val, array('A', 'G'))){
                        $objPHPExcel->getSheet($k)->getColumnDimension($val)->setWidth(12);
                    } else {
                        $objPHPExcel->getSheet($k)->getColumnDimension($val)->setWidth(10);
                    }
                } elseif ($k == 1) {
                    if (in_array($val, array('A', 'B', 'H'))) {
                        $objPHPExcel->getSheet($k)->getColumnDimension($val)->setWidth(20);
                    } else {
                        $objPHPExcel->getSheet($k)->getColumnDimension($val)->setWidth(10);
                    }
                } else {
                    if (in_array($val, array('B', 'C', 'D', 'F'))) {
                        $objPHPExcel->getSheet($k)->getColumnDimension($val)->setWidth(22);
                    } elseif (in_array($val, array('A'))) {
                        $objPHPExcel->getSheet($k)->getColumnDimension($val)->setWidth(8);
                    } else {
                        $objPHPExcel->getSheet($k)->getColumnDimension($val)->setWidth(12);
                    }
                }
                
                // $objPHPExcel->getSheet($k)->getColumnDimension($val)->setWidth(12);
                //自动设置单元格宽度   例：
                // $objPHPExcel->getSheet($k)->getColumnDimension($val)->setAutoSize(true);
                //设置单位格内容
                $objPHPExcel->getSheet($k)->setCellValue($val.$start_row, $col_title_arr[$key]);
                //格式化数字为字符串，避免导致数字默认右对齐单元格
                $objPHPExcel->getSheet($k)->getStyle($val)->getNumberFormat()->setFormatCode("@");
                //设置自动换行
                $objPHPExcel->getSheet($k)->getStyle($val)->getAlignment()->setWrapText(true);
                // 标题字体加粗
                $objPHPExcel->getSheet($k)->getStyle($val.$start_row)->getFont()->setBold(true);
                //设置对齐方式
                $objPHPExcel->getSheet($k)->getStyle($val)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                $objPHPExcel->getSheet($k)->getStyle($val.$start_row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                $objPHPExcel->getSheet($k)->getStyle($val)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
                $objPHPExcel->getSheet($k)->getStyle($val.$start_row)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
                
                // 设置边框
                $objPHPExcel->getSheet($k)->getStyle($val.$start_row)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
                $objPHPExcel->getSheet($k)->getStyle($val.$start_row)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THICK);
                $objPHPExcel->getSheet($k)->getStyle($val.$start_row)->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
                $objPHPExcel->getSheet($k)->getStyle($val.$start_row)->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
                
                $i++;
                if ($start_row == 2 && $i > 9) {
                    // 设置背景色
                    //设置填充颜色
                    $objPHPExcel->getSheet($k)->getStyle($val.$start_row)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
                    $objPHPExcel->getSheet($k)->getStyle($val.$start_row)->getFill()->getStartColor()->setARGB('FF92D050');
                    
                    // 设置完成后冻结行(冻结前两行则设置为A3(只冻结行))
                    $objPHPExcel->getSheet($k)->freezePane('A3');
                } else {
                    // 必须在设置完值之后才可以
                    $objPHPExcel->getSheet($k)->freezePane('A2');
                }
            }
            $start_row ++;
            
            if (isset($col_title_alias_arr[1]) && is_array($col_title_alias_arr[1])) {
                $col_title_alias_arr = array_merge($col_title_alias_arr[0], $col_title_alias_arr[1]);
            }
            foreach ($data_list[$k]['data'] as $data_key => $val) {
                foreach ($col_arr as $col_key => $col) {
                    $place = $col.$start_row;
                    $col_content = $col_title_arr[$col_key];
                    
                    $value = '';
                    
                    $val_keys = array_keys($val);
                    if (in_array($col_title_alias_arr[$col_key], $val_keys)) {
                        $value = $val[$col_title_alias_arr[$col_key]];
                    }
                    
                    $objPHPExcel->getSheet($k)->setCellValue($place, $value);
                    
                }
                $start_row++;
            }
            
            $k++;
        }
        //设置当前的sheet索引
        $objPHPExcel->setActiveSheetIndex(0);
        
        //设置页面类型
        header("Content-Type: application/vnd.ms-excel");
        //用于下载的文件头
        header("Content-Type: application/force-download");
        header("Content-Type: application/download");
        header("Content-Type: application/octet-stream");
        
        $title = trim($file_name).date('YmdHis').".xls";
        // ff正常，ie不正常，下面是从网站找到的解决方案
        if (preg_match('/MSIE/',$_SERVER['HTTP_USER_AGENT'])) {
            $title = rawurlencode($title);
        }
        header("Content-Disposition: attachment; filename=".$title);
        header("Content-Transfer-Encoding: binary");
        //最后修改时间
        header("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT");
        //不使用缓存
        header('Cache-Control: ust-revalidate, post-check=0, pre-check=0');
        header('Cache-Control: public');
        header("Expires: 0");
        header("Pragma: public");
        $objWriter->save('php://output');
        exit;
        
    }
}



?>